using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using System.Collections;

//The purpose of this class and its methods is to handle the following database related operations in the project
//Connection Creation
//DDL Operation
//DML Operation
//DQL Operation
//Connection Close and resource cleanup



    public class DBHelper
    {
        public static MySqlConnection objDbCon;

        public DBHelper()
        {

        }

        //This function is used to open a Database connection with Database Server. 
        //It accepts no parameter and return MySqlConnection object.
        public static MySqlConnection openDBConnection()
        {
            try
            {
                objDbCon = new MySqlConnection();//creating new MySqlConnection object
                //Connection string information is specific to the Database server.
                //Change the information according to your Database server information

                objDbCon.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
                objDbCon.Open();
            }
            catch (Exception e)
            {
                objDbCon = null;
            }
            return objDbCon;
        }


        //This function is used to cleanup the Database connection object it accepts no parameter and also does not return any value.
        public static void cleanupConnection()
        {
            try
            {
                if (objDbCon != null)
                {
                    objDbCon.Close();
                }
            }
            catch (Exception e)
            {

            }
        }

        //This function is used to get the data from the database in MySqlDataReader object. 
        //This function accepts sql string as a parameter and returns a MySqlDataReader object
        public static MySqlDataReader GetDataFromDBInReader(string p_sql)
        {
            MySqlDataReader objSqlRD = null;//creating MySqlDataReader object
            MySqlCommand objSqlCmd = null;//creating MySqlCommand object
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objSqlCmd = objDbCon.CreateCommand();
                    objSqlCmd.CommandText = p_sql;
                    objSqlRD = objSqlCmd.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                ErrorHandler.WriteError(e, "SQL->" + p_sql);
            }
            finally
            {
                
            }
            return objSqlRD;
        }

        //This function is used to get data from Database in DataSet object. This function accepts sql string as parameter and returns DataSet object
        public static DataSet GetDataFromDBInDataSet(string p_sql)
        {
            DataSet objDbDS = null;//creating DataSet object
            MySqlDataAdapter objSqlDataAdapter = null;//creating MySqlDataAdapter object
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objDbDS = new DataSet();
                    objSqlDataAdapter = new MySqlDataAdapter(p_sql, objDbCon);
                    objSqlDataAdapter.Fill(objDbDS);
                }
            }
            catch (Exception e)
            {
                ErrorHandler.WriteError(e, "SQL->" + p_sql);
            }
            finally
            {
                cleanupConnection();//cleanup connection
            }
            return objDbDS;
        }

        //This function is used to get data from Database in DataSet object. This function accepts sql string as parameter and returns DataSet object
        public static DataSet GetDataFromDBInDataSet(string p_sql, string dataSetName)
        {
            DataSet objDbDS = null;//creating DataSet object
            MySqlDataAdapter objSqlDataAdapter = null;//creating MySqlDataAdapter object
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objDbDS = new DataSet(dataSetName);
                    objSqlDataAdapter = new MySqlDataAdapter(p_sql, objDbCon);
                    objSqlDataAdapter.Fill(objDbDS);
                }
            }
            catch (Exception e)
            {
                ErrorHandler.WriteError(e, "SQL->" + p_sql);
            }
            finally
            {
                cleanupConnection();//cleanup connection
            }
            return objDbDS;
        }

        //This function is used to get data from the Database in DataTable object. This function accepts sql string as parameter and returns DataTable object
        public static DataTable GetDataFromDbInDataTable(string p_sql)
        {
            DataTable dt = null;//creating DataSet object
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    DataSet ds = GetDataFromDBInDataSet(p_sql);
                    if (ds.Tables.Count > 0)
                        dt = ds.Tables[0];
                }
            }
            catch (Exception e)
            {
                ErrorHandler.WriteError(e, "SQL->" + p_sql);
            }
            finally
            {
                cleanupConnection();//cleanup connection
            }
            return dt;
        }

        //This function is used to execute DML quries(insert/update)in Database.This function accepts string ArrayList of DML queries as parameter and returns a boolean value True on success and False on failure.
        //This function can execute multiple DMLs because it accept DML queries as parameter in array form.
        public static bool InsertUpdateDataInDb(string[] p_sqls)
        {
            if (p_sqls.Length == 0)
                return false;
            string SQL = null;
            bool b_status = false;
            MySqlDataAdapter objSqlDataAdapter = null;//creating SqlDataAdapter object
            MySqlCommand objSqlCmd = null;//creating SqlCommand object
            MySqlTransaction objSqlTransaction = null;//creating SqlTransaction object
            int cnt = -1;
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objSqlTransaction = objDbCon.BeginTransaction();
                    objSqlCmd = objDbCon.CreateCommand();
                    objSqlCmd.Transaction = objSqlTransaction;

                    for (int i = 0; i < p_sqls.GetLength(0); i++)//Iteration for executing multiple DMLs
                    {
                        if (p_sqls[i] != null && p_sqls[i].Length > 0)
                        {
                            SQL = p_sqls[i];
                            objSqlCmd.CommandText = p_sqls[i];
                            cnt = objSqlCmd.ExecuteNonQuery();
                        }
                    }
                    //if (b_status == false) //If Not all DMLs were successfull- Rollback
                    //    objSqlTransaction.Rollback();
                    //else                   //If All DMLs were successfull- Commit
                        objSqlTransaction.Commit();
                        b_status = true;
                }
            }
            catch (Exception e)
            {
                objSqlTransaction.Rollback();
                b_status = false;
                if (SQL != null)
                    ErrorHandler.WriteError(e, "SQL->" + SQL);
                else
                    ErrorHandler.WriteError(e);
            }
            finally
            {
                if (objSqlDataAdapter != null)
                    objSqlDataAdapter.Dispose();//disposing SqlDataAdapter object
                if (objSqlCmd != null)
                    objSqlCmd.Dispose();//disposing SqlCommand object
                cleanupConnection();
            }
            return b_status;
        }

        public static bool InsertUpdateDataInDb(string[] p_sqls, ref long lastInsertedId)
        {
            if (p_sqls.Length == 0)
                return false;
            string SQL = null;
            bool b_status = false;
            MySqlDataAdapter objSqlDataAdapter = null;//creating SqlDataAdapter object
            MySqlCommand objSqlCmd = null;//creating SqlCommand object
            MySqlTransaction objSqlTransaction = null;//creating SqlTransaction object
            int cnt = -1;
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objSqlTransaction = objDbCon.BeginTransaction();
                    objSqlCmd = objDbCon.CreateCommand();
                    objSqlCmd.Transaction = objSqlTransaction;

                    for (int i = 0; i < p_sqls.GetLength(0); i++)//Iteration for executing multiple DMLs
                    {
                        if (p_sqls[i] != null && p_sqls[i].Length > 0)
                        {
                            SQL = p_sqls[i];
                            objSqlCmd.CommandText = p_sqls[i];
                            cnt = objSqlCmd.ExecuteNonQuery();
                            lastInsertedId = objSqlCmd.LastInsertedId;
                        }
                    }
                    objSqlTransaction.Commit();
                    b_status = true;
                }
            }
            catch (Exception e)
            {
                objSqlTransaction.Rollback();
                b_status = false;
                if (SQL != null)
                    ErrorHandler.WriteError(e, "SQL->" + SQL);
                else
                    ErrorHandler.WriteError(e);
            }
            finally
            {
                if (objSqlDataAdapter != null)
                    objSqlDataAdapter.Dispose();//disposing SqlDataAdapter object
                if (objSqlCmd != null)
                    objSqlCmd.Dispose();//disposing SqlCommand object
                cleanupConnection();
            }
            return b_status;
        }

        //This function is used to execute DML quries(insert/update)in Database.This function accepts string ArrayList of DML queries as parameter and returns a boolean value True on success and False on failure.
        //This function can execute multiple DMLs because it accept DML queries as parameter in array form.
        public static bool InsertUpdateDataInDb(string p_sql, ref long lastInsertedId)
        {
            if (p_sql.Length == 0)
                return false;
            string SQL = null;
            bool b_status = false;
            MySqlDataAdapter objSqlDataAdapter = null;//creating SqlDataAdapter object
            MySqlCommand objSqlCmd = null;//creating SqlCommand object
            MySqlTransaction objSqlTransaction = null;//creating SqlTransaction object
            int cnt = -1;
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objSqlTransaction = objDbCon.BeginTransaction();
                    objSqlCmd = objDbCon.CreateCommand();
                    objSqlCmd.Transaction = objSqlTransaction;
                    objSqlCmd.CommandText = p_sql;
                    cnt = objSqlCmd.ExecuteNonQuery();
                    lastInsertedId = objSqlCmd.LastInsertedId;
                    objSqlTransaction.Commit();
                    b_status = true;
                }
            }
            catch (Exception e)
            {
                objSqlTransaction.Rollback();
                b_status = false;
                if (p_sql != null)
                    ErrorHandler.WriteError(e, "SQL->" + p_sql);
                else
                    ErrorHandler.WriteError(e);
            }
            finally
            {
                if (objSqlDataAdapter != null)
                    objSqlDataAdapter.Dispose();//disposing SqlDataAdapter object
                if (objSqlCmd != null)
                    objSqlCmd.Dispose();//disposing SqlCommand object
                cleanupConnection();
            }
            return b_status;
        }

        //This function is used to execute DML quries(insert/update)in Database.This function accepts string ArrayList of DML queries as parameter and returns a boolean value True on success and False on failure.
        //This function can execute multiple DMLs because it accept DML queries as parameter in array form.
        public static bool InsertUpdateDataInDb(string p_sql)
        {
            if (p_sql.Length == 0)
                return false;
            string SQL = null;
            bool b_status = false;
            MySqlDataAdapter objSqlDataAdapter = null;//creating SqlDataAdapter object
            MySqlCommand objSqlCmd = null;//creating SqlCommand object
            MySqlTransaction objSqlTransaction = null;//creating SqlTransaction object
            int cnt = -1;
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objSqlTransaction = objDbCon.BeginTransaction();
                    objSqlCmd = objDbCon.CreateCommand();
                    objSqlCmd.Transaction = objSqlTransaction;
                    objSqlCmd.CommandText = p_sql;
                    cnt = objSqlCmd.ExecuteNonQuery();
                    objSqlTransaction.Commit();
                    b_status = true;
                }
            }
            catch (Exception e)
            {
                objSqlTransaction.Rollback();
                b_status = false;
                if (p_sql != null)
                    ErrorHandler.WriteError(e, "SQL->" + p_sql);
                else
                    ErrorHandler.WriteError(e);
            }
            finally
            {
                if (objSqlDataAdapter != null)
                    objSqlDataAdapter.Dispose();//disposing SqlDataAdapter object
                if (objSqlCmd != null)
                    objSqlCmd.Dispose();//disposing SqlCommand object
                cleanupConnection();
            }
            return b_status;
        }

        //This function is used to execute DML quries(insert/update)in Database.This function accepts string ArrayList of DML queries as parameter and returns a boolean value True on success and False on failure.
        //This function can execute multiple DMLs because it accept DML queries as parameter in array form.
        //If IgnoreError flag is passed as True, then any erroroneous DML statement will be ignored and
        //execution of the next DML will continue.
        public static bool InsertUpdateDataInDb(string[] p_sqls, bool p_bIgnoreErrors)
        {
            if (p_sqls.Length == 0)
                return false;
            
            string SQL = null;
            bool b_status = true;
            MySqlDataAdapter objSqlDataAdapter = null;//creating SqlDataAdapter object
            MySqlCommand objSqlCmd = null;//creating SqlCommand object
            MySqlTransaction objSqlTransaction = null;//creating SqlTransaction object
            int cnt = -1;
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null)//successful connection
                {
                    objSqlTransaction = objDbCon.BeginTransaction();
                    objSqlCmd = objDbCon.CreateCommand();
                    objSqlCmd.Transaction = objSqlTransaction;

                    for (int i = 0; i < p_sqls.GetLength(0); i++)//Iteration for executing multiple DMLs
                    {
                        if (p_sqls[i] != null && p_sqls[i].Length > 0)
                        {
                            SQL = p_sqls[i];
                            objSqlCmd.CommandText = p_sqls[i];
                            try
                            {
                                cnt = objSqlCmd.ExecuteNonQuery();
                            }
                            catch (Exception exp)
                            {
                                if (!p_bIgnoreErrors)
                                {
                                    objSqlTransaction.Rollback();
                                    b_status = false;
                                    if (SQL != null)
                                        ErrorHandler.WriteError(exp, "SQL->" + SQL);
                                    else
                                        ErrorHandler.WriteError(exp);
                                    break;
                                }
                                if (SQL != null)
                                    ErrorHandler.WriteError(exp, "SQL->" + SQL);
                                else
                                    ErrorHandler.WriteError(exp);
                            }
                        }
                    }
                    //All DMLs were successfull- Commit
                    objSqlTransaction.Commit();
                }
            }
            catch (Exception e)
            {
                objSqlTransaction.Rollback();
                b_status = false;
                if (SQL != null)
                    ErrorHandler.WriteError(e, "SQL->" + SQL);
                else
                    ErrorHandler.WriteError(e);
            }
            finally
            {
                if (objSqlDataAdapter != null)
                    objSqlDataAdapter.Dispose();//disposing SqlDataAdapter object
                if (objSqlCmd != null)
                    objSqlCmd.Dispose();//disposing SqlCommand object
                cleanupConnection();
            }
            return b_status;
        }

        //This function is used to delete the record from the Database. 
        //This function accepts DML queries(delete) as parameter in array form and returns a boolean value as True for Success and False for Failure.
        public static bool DeleteDataFromDb(string[] p_sqls)
        {
            if (p_sqls.Length == 0)
                return false;

            string SQL = null;
            bool b_status = true;
            MySqlDataAdapter objSqlDataAdapter = null;//creating SqlDataAdapter object
            MySqlCommand objSqlCmd = null;//creating SqlCommand object
            MySqlTransaction objSqlTransaction = null;//creating SqlTransaction object
            int cnt = -1;
            try
            {
                //Open DataBase Connection
                openDBConnection();
                if (objDbCon != null) // Successful connection
                {
                    objSqlTransaction = objDbCon.BeginTransaction();
                    objSqlCmd = objDbCon.CreateCommand();
                    objSqlCmd.Transaction = objSqlTransaction;
                    //Iterate thru SQL statement collection
                    for (int i = 0; i < p_sqls.GetLength(0); i++)//Iteration for executing multiple DMLs
                    {
                        if (p_sqls[i] != null && p_sqls[i].Length > 0)
                        {
                            SQL = p_sqls[i];
                            objSqlCmd.CommandText = p_sqls[i];
                            try
                            {
                                cnt = objSqlCmd.ExecuteNonQuery();
                            }
                            catch (Exception exp)
                            {
                                b_status = false;
                                if (SQL != null)
                                    ErrorHandler.WriteError(exp, "SQL->" + SQL);
                                else
                                    ErrorHandler.WriteError(exp);
                                break;
                            }
                        }
                    }
                    if (b_status == false) //If Not all DMLs were successfull- Rollback
                        objSqlTransaction.Rollback();
                    else                   //If All DMLs were successfull- Commit
                        objSqlTransaction.Commit();
                }
            }
            catch (Exception e)
            {
                objSqlTransaction.Rollback();
                if (SQL != null)
                    ErrorHandler.WriteError(e, "SQL->" + SQL);
                else
                    ErrorHandler.WriteError(e);
                b_status = false;
            }
            finally
            {
                if (objSqlDataAdapter != null)
                    objSqlDataAdapter.Dispose();//disposing SqlDataAdapter object
                if (objSqlCmd != null)
                    objSqlCmd.Dispose();//disposing SqlCommand object
                cleanupConnection();
            }
            return b_status;
        }

        //This function is used to open a check Database availability.
        public bool checkDBConnectionStatus()
        {
            bool status = false;
            try
            {
                MySqlConnection l_objDbCon = new MySqlConnection();//creating new SqlConnection object
                //Connection string information is specific to the Database server change the information according to your Database server information
                objDbCon.ConnectionString = ConfigurationSettings.AppSettings.Get("ConnectionString");
                objDbCon.Open();
                status = true;
                objDbCon.Close();
            }
            catch (Exception e)
            {
                ErrorHandler.WriteError(e);
            }
            return status;
        }
    }

